"""
This script is an example of using the OpenAI API to create various interactions with a ChatGLM3 model.
It includes functions to:

1. Conduct a basic chat session, asking about weather conditions in multiple cities.
2. Initiate a simple chat in Chinese, asking the model to tell a short story.
3. Retrieve and print embeddings for a given text input.

Each function demonstrates a different aspect of the API's capabilities, showcasing how to make requests
and handle responses.
"""
import json

import re
import mysql.connector

from message_management.models import BuildInfo
from utils.model_api_o import ModelAPI

from django.conf import settings

BUILD_DATA = settings.BUILD_DATA

# base_url = "http://10.206.60.14:8002/v1/"
# client = OpenAI(api_key="EMPTY", base_url=base_url)
llm = ModelAPI()
client = llm.client

model_sql = {
    "gongyisusong": {
        "sql_template": """
    SELECT 
        c.bmsah AS '案件编号', 
        c.ajmc AS '案件名称', 
        c.slrq AS '受理日期', 
        c.cbdw AS '承办单位代码', 
        b.xm AS '嫌疑人姓名', 
        b.zjhm AS '证件号码'
    FROM 
        law.t_tyyw_xj_ysgs_aj c
    LEFT JOIN 
        law.t_tyyw_xj_ysgs_xyr b ON b.bmsah = c.bmsah
    WHERE 
        {where_clause}
    ORDER BY 
        c.cbdw
        """,
        "key_name": "c.ajmc"
    },
    "sifapaimai": {
        "sql_template": """
        select *
from law.data_wlsf
where {where_clause}
""",
        "key_name": "jmxz"
    },
    "tiaojiechaoqi": {
        "sql_template": """
      select *
from law.data_fayuan_maotiao
where 调解时长（天） > {value}
      """
    },
    "zhiyefangdairen": {
        "sql_template": """
        SELECT
    xm AS '姓名',
    zjhm AS '证件号码',
    reason AS '案由',
    COUNT(zjhm) AS '案件数量',
    ajmc AS '案件名称',
    b.bmsah AS '案件编号',
    a.docContent_noTag AS '案件内容'

from (SELECT caseNo, reason, docContent_noTag
      FROM law.data_wenshu
      where reason like '民间借贷'
        and docContent_noTag like '%公告送达%'
        and docContent_noTag like '%被告未出庭%') a
         left join law.t_tyyw_xj_ysgs_xyr b on a.caseNo = b.YSPJSWH
         left join law.t_tyyw_xj_ysgs_aj c1 on c1.bmsah = b.bmsah
group by zjhm
HAVING COUNT(zjhm) > {value}
        """
    },
    "weixianjiashi": {
        "sql_template": """
        select
    distinct
    B.xm    '姓名',
      B.bmsah '部门受案号',
       C.ajmc  '案件名称',
       A.hxjcz '呼吸检测值',
       xyjcz   '血液检测值',
       B.zjhm  '证件号码'
from law.ocr_jcz A
         left join law.t_tyyw_xj_ysgs_xyr B on A.bmsah = B.bmsah
         left join law.t_tyyw_xj_ysgs_aj C on A.bmsah = C.bmsah
where xyjcz < hxjcz
  and xyjcz < (1-1.0*{value}/100) * hxjcz
        """
    }
}


def construct_sql(model_key, keywords):
    # 从字典中获取SQL模板和关键字段名
    sql_template = model_sql[model_key]["sql_template"]

    if model_key == "gongyisusong":
        key_name = model_sql[model_key]["key_name"]
        # 构建WHERE子句，为每个关键词生成LIKE语句
        keyword_conditions = [f"{key_name} LIKE '%{keyword.strip()}%'" for keyword in keywords]
        where_clause = " OR ".join(keyword_conditions)
        # 将WHERE子句插入SQL模板
        final_sql = sql_template.format(where_clause=where_clause)
    elif model_key == "zhiyefangdairen" or model_key == "tiaojiechaoqi" or model_key == "weixianjiashi":
        # 将数字插入SQL模板
        final_sql = sql_template.format(value=keywords)
    return final_sql


llm = ModelAPI()


def function_chat(content):
    messages = [{"role": "user", "content": content}]
    tools = [
        {
            "type": "function",
            "function": {
                "name": "gongyisusong",
                "description": "公益诉讼模型",
                "parameters": {
                    "type": "object",
                    "properties": {
                        "Keywords": {
                            "type": "array",
                            "description": "模型的构造条件,一般是多个关键词",
                        },
                    },
                    "required": ["Keywords"],
                },
            },
        },
        {
            "type": "function",
            "function": {
                "name": "zhiyefangdairen",
                "description": "职业放贷人隐瞒事实法律监督模型",
                "parameters": {
                    "type": "object",
                    "properties": {
                        "value": {
                            "type": "integer",
                            "description": "模型的构造条件,一般是某个数字",
                        },
                    },
                    "required": ["value"],
                },
            },
        },
        {
            "type": "function",
            "function": {
                "name": "tiaojiechaoqi",
                "description": "民事审判诉前调解超期法律监督模型",
                "parameters": {
                    "type": "object",
                    "properties": {
                        "value": {
                            "type": "integer",
                            "description": "模型的构造条件,一般是某个数字",
                        },
                    },
                    "required": ["value"],
                },
            },
        },
        {
            "type": "function",
            "function": {
                "name": "sifapaimai",
                "description": "网络司法拍卖法律监督模型",
                "parameters": {
                    "type": "object",
                    "properties": {
                        "Keywords": {
                            "type": "array",
                            "description": "模型的构造条件，文本句字",
                        },
                    },
                    "required": ["Keywords"],
                },
            },
        },
        {
            "type": "function",
            "function": {
                "name": "weixianjiashi",
                "description": "危险驾驶（呼吸与血液检测之相差标准）法律监督模型",
                "parameters": {
                    "type": "object",
                    "properties": {
                        "value": {
                            "type": "integer",
                            "description": "模型的构造条件，参数是“血液监测数值和呼吸式酒精监测数值相差的百分比”，是一个百分数",
                        },
                    },
                    "required": ["value"],
                },
            },
        },

    ]
    # 你的作用是根据messages在tools中选择正确的工具，并返回工具的参数

    response = client.chat.completions.create(
        model="chatglm3-6b",
        messages=messages,
        tools=tools,
        tool_choice="auto",
    )
    if response:
        content = response.choices[0].message.content
        print(content)

        # 第一步：提取"gongyisusong"
        # 假设"gongyisusong"位于首行，直接使用split取得第一行
        main_content = content.strip().split('\n')[0].strip()

        # 字符串content中查询是否有"Keywords"字符
        # 两种模式：1.单个参数，2.多个参数
        flag = "Keywords" not in content

        params = None
        if not flag:
            # 第二步：提取Keywords内的值
            # 使用正则表达式找到Keywords的数组
            keywords_match = re.search(r"Keywords=\[([^\]]+)\]", content)
            keywords = []
            if keywords_match:
                # 分割找到的字符串，去除引号，转换为列表
                keywords = keywords_match.group(1).replace("'", "").split(',')
            params = keywords
        else:
            # 使用正则表达式匹配模式
            match = re.search(r"tool_call\(value=(\d+)\)", content)

            # 提取匹配的值
            if match:
                value = match.group(1)  # group(1) 指的是第一个括号内匹配的内容
                print("Extracted value:", value)
            else:
                print("No match found")
            params = value

        # 返回处理后的值
        return main_content, params
        # return content
    else:
        print("Error:", response.status_code)


def run_sql(sql_query, content):
    connection = mysql.connector.connect(
        host=BUILD_DATA['host'],
        user=BUILD_DATA['user'],
        password=BUILD_DATA['password'],
        database=BUILD_DATA['database']
    )
    cursor = connection.cursor()

    paginated_sql_str = f"{sql_query.replace(';', '')} LIMIT 10 OFFSET 0"
    # Execute the SQL query
    cursor.execute(paginated_sql_str)
    headers = [description[0] for description in cursor.description]
    yield json.dumps({
        'text': f"{'| ' + ' | '.join(headers) + ' |'}\n"}) + "&&##&&"
    yield json.dumps({
        'text': f"{'| ' + ' | '.join(['---'] * len(headers)) + ' |'}\n"}) + "&&##&&"

    # Fetching and yielding each row of results
    for row in cursor.fetchall():
        yield json.dumps({
            'text': f"{'| ' + ' | '.join(str(item) for item in row) + ' |'}\n"}) + "&&##&&"

    # yield final_sql

    id = BuildInfo.create_build(sql_query, content)
    yield json.dumps({
        'text': f"\n\n[更多信息 >](searchBuild?id={id})\n"}) + "&&##&&"
    # Close the connection
    cursor.close()
    connection.close()
    # return cursor, connection


def build_model(content):
    # 选择模型和关键词
    model_select, keywords = function_chat(content)

    print("Main Content:", model_select)
    print("Keywords:", keywords)  # 去除可能的空格

    # # 调用函数生成SQL
    final_sql = construct_sql(model_select, keywords)
    print(final_sql)
    # # 执行并返回结果
    return run_sql(final_sql, content)



if __name__ == "__main__":
    #     res = build_model(
    #         """
    #         构建职业放贷人隐瞒事实法律监督模型，模型规则为：某人在民间借贷案件中，作为原告出现次数大于等于3次
    # 查询出相关的案件线索
    # """
    #     )
    #     for r in res:
    #         print(r)

    #     res = build_model(
    #         """
    #         构建网络司法拍卖法律监督模型，模型规则为，竞买须知中包含以下关键句：
    #         1. 本院不承担瑕疵担保、房屋瑕疵担保由买受人自行核实、具体税费由买受人自行了解、欠费由买受人承担等关键字的数据
    #         2. 未依法公示拍卖依据、未上传网络司法拍卖所依据的法律条文、未上传评估报告、执行人员未对财产进行调查等情形
    # 查询出相关的案件线索
    # """
    #     )
    #     for r in res:
    #         print(r)
#     res = build_model(
#         """
#         构建民事审判诉前调解超期法律监督模型，模型规则为：调解时长（天）超过30天的案件
# """
#     )
    res = build_model(
        """
        构建危险驾驶（呼吸与血液检测之相差标准）法律监督模型，模型规则为：
        1.筛查业务系统中有关酒驾、危险驾驶的案件信息；
2.筛查起诉意见书（world版）中有关“呼气式酒精检测”和“血液酒精监测”的关键字；
3.呼吸式酒精检测与血液酒精监测数值对比,血液监测数值低于呼吸式酒精监测数值,且相差超过20%的人员数据信息。
"""
    )
    for r in res:
        print(r)

    # simple_chat(use_stream=False)
    # simple_chat(use_stream=True)
    # embedding()
    # model_select, keywords = function_chat()
    #
    # print("Main Content:", model_select)
    # print("Keywords:", keywords)  # 去除可能的空格
    #
    # # 调用函数生成SQL
    # final_sql = construct_sql(model_select, keywords)
    # print(final_sql)

# def simple_chat(use_stream=True):
#     messages = [
#         {
#             "role": "system",
#             "content": "You are ChatGLM3, a large language model trained by Zhipu.AI. Follow the user's "
#                        "instructions carefully. Respond using markdown.",
#         },
#         {
#             "role": "user",
#             "content": "你好，请你用生动的话语给我讲一个小故事吧"
#         }
#     ]
#     response = client.chat.completions.create(
#         model="chatglm3-6b",
#         messages=messages,
#         stream=use_stream,
#         max_tokens=256,
#         temperature=0.8,
#         presence_penalty=1.1,
#         top_p=0.8)
#     if response:
#         if use_stream:
#             for chunk in response:
#                 print(chunk.choices[0].delta.content)
#         else:
#             content = response.choices[0].message.content
#             print(content)
#     else:
#         print("Error:", response.status_code)
#
#
# def embedding():
#     response = client.embeddings.create(
#         model="bge-large-zh-1.5",
#         input=["你好，给我讲一个故事，大概100字"],
#     )
#     embeddings = response.data[0].embedding
#     print("嵌入完成，维度：", len(embeddings))
